﻿Imports System.Data
Imports System.Data.SqlClient

Public Class RotasDAL
    Sub New()

    End Sub

    Function inserir(cid_origem As Integer, cid_destino As Integer, distancia As Double, descricao As String) As Integer
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim id As Integer
        Try
            cmd.CommandText = "insert into rotas(cid_origem, cid_destino, rot_descricao, rot_distancia, rot_ativa) " & _
                "values(@cid_origem, @cid_destino, @rot_descricao, @rot_distancia, 1)"
            cmd.Parameters.Add(New SqlParameter("@cid_origem", SqlDbType.Int)).Value = cid_origem
            cmd.Parameters.Add(New SqlParameter("@cid_destino", SqlDbType.Int)).Value = cid_destino
            cmd.Parameters.Add(New SqlParameter("@rot_distancia", SqlDbType.Float)).Value = distancia
            cmd.Parameters.Add(New SqlParameter("@rot_descricao", SqlDbType.Text)).Value = descricao
            id = objD.executacomandoAI(cmd, "rotas")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return id
    End Function

    Sub alterar(ByVal rot_codigo As Integer, descricao As String, ativa As Boolean)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "update rotas set rot_descricao=@rot_descricao, " & _
                "rot_ativa=@rot_ativa where rot_codigo=@rot_codigo"
            cmd.Parameters.Add(New SqlParameter("@rot_descricao", SqlDbType.Text)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@rot_ativa", SqlDbType.Bit)).Value = ativa
            cmd.Parameters.Add(New SqlParameter("@rot_codigo", SqlDbType.Int)).Value = rot_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Sub excluir(rot_codigo As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "delete from rotas where rot_codigo=@rot_codigo"
            cmd.Parameters.Add(New SqlParameter("@rot_codigo", SqlDbType.Int)).Value = rot_codigo
            objD.executacomando(cmd)
        Catch ex As SqlException
            If ex.Number = 547 Then
                Throw New Exception("Exclusão não pode ser concluída. Existem motoristas designados para essa rota.")
            End If
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select * from rotas where rot_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizar(status As Boolean, campo As String, cidade As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Dim filtro As String = ""
        Try
            If status Then
                filtro = " and r.rot_ativa=1"
            End If
            cmd.CommandText = "select r.rot_codigo, r.cid_origem, (select c.nom_cidade where r.cid_origem = c.cod_cidade) Origem, " +
                "r.cid_destino as destino, (select cd.nom_cidade where r.cid_destino = cd.cod_cidade) Destino, " +
                " r.rot_descricao, r.rot_distancia, r.rot_ativa " +
                "from rotas r inner join cidade c on r.cid_origem = c.cod_cidade inner join cidade cd on r.cid_destino = cd.cod_cidade " +
                "where " + campo + " like @cidade" + filtro
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = "%" + cidade + "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizar(origem As String, destino As String, status As Boolean) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Dim filtro As String = ""
        Try
            If status Then
                filtro = " and r.rot_ativa=1"
            End If
            cmd.CommandText = "select r.rot_codigo, r.cid_origem, (select c.nom_cidade where r.cid_origem = c.cod_cidade) Origem, " +
                "r.cid_destino as destino, (select cd.nom_cidade where r.cid_destino = cd.cod_cidade) Destino " +
                "from rotas r inner join cidade c on r.cid_origem = c.cod_cidade inner join cidade cd on r.cid_destino = cd.cod_cidade " +
                "where c.nom_cidade like @origem and cd.nom_cidade like @destino" + filtro
            cmd.Parameters.Add(New SqlParameter("@origem", SqlDbType.VarChar)).Value = "%" + origem + "%"
            cmd.Parameters.Add(New SqlParameter("@destino", SqlDbType.VarChar)).Value = "%" + destino + "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT rot_codigo = max(rot_codigo) FROM rotas "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT rot_codigo = min(rot_codigo) FROM rotas "
            End If
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT rot_codigo FROM rotas WHERE rot_codigo = (SELECT MIN(rot_codigo) FROM rotas WHERE rot_codigo > @codigo)"

            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT rot_codigo FROM rotas  WHERE rot_codigo = (SELECT MAX(rot_codigo) FROM rotas WHERE rot_codigo < @codigo)"
            End If
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarCidade(estado As Integer, cidade As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            cmd.CommandText = "select p.nom_pais, e.sgl_estado, c.cod_cidade, c.nom_cidade from pais p, estado e, cidade c " & _
                "where p.cod_pais=e.cod_pais and e.cod_estado=c.cod_estado " & _
                "and e.cod_estado=@estado and c.nom_cidade like @cidade"
            cmd.Parameters.Add(New SqlParameter("@estado", SqlDbType.Int)).Value = estado
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = cidade + "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarCidade(cidade As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            cmd.CommandText = "select nom_cidade from cidade where cod_cidade=@cidade"
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.Int)).Value = cidade
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

End Class